﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Trade.Model;

namespace Trade.DAL
{
    public class AccountsPayable
    {
        private const string SQL_SELECT_ACCOUNTSPAYABLE = "Select A.BillNo, A.Category, S.SubjectName as CategoryName, A.PayDate, A.Employee, A.ReferenceNo, A.Amount, A.Account, A.EntryDate, A.Remark, A.RecordId from AccountsPayable A left join SubjectMessage S on A.Category = S.SubjectId";
        private const string SQL_WHERE = " where 1 > 2";
        private const string SQL_WHERE_RECORDID = " where A.RecordId = @RecordId";

        public DataTable GetAccountsPayableList()
        {
            return SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_ACCOUNTSPAYABLE, null);
        }

        //创建数据表结构
        public DataTable AccountsPayableTemplate()
        {
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_ACCOUNTSPAYABLE + SQL_WHERE, null))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //根据指定的记录编号读取相应的应付应收记录
        public DataRow GetAccountsPayableByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            DataTable dt = SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_ACCOUNTSPAYABLE + SQL_WHERE_RECORDID, parm);
            DataRow dr = dt.Rows[0];
            return dr;
        }

        public AccountsPayableInfo GetAccountsPayableInfoByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            AccountsPayableInfo accountsPayableInfo = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_ACCOUNTSPAYABLE + SQL_WHERE_RECORDID, parm))
            {
                while (dr.Read())
                {
                    accountsPayableInfo = new AccountsPayableInfo(dr.GetString(0), dr.GetInt32(1), dr.GetDateTime(3), dr.GetString(4), dr.GetString(5), dr.GetDecimal(6), dr.GetString(7), dr.GetDateTime(8),
                                                                dr.GetString(9), dr.GetInt32(10));
                }
            }
            return accountsPayableInfo;
        }

        //生成新的单据编号
        public string GenAccountPayableBillNo(string curDate)
        {
            string cmdString = "exec Usp_SerialNo @p_SerialNoName, @p_FieldName, @p_TableName, @p_PrefixTime, @p_RSerialNo out";
            string billNo = "";

            SqlParameter[] parms = {
                new SqlParameter("@p_SerialNoName", SqlDbType.VarChar, 30),
                new SqlParameter("@p_FieldName", SqlDbType.VarChar, 30),
                new SqlParameter("@p_TableName", SqlDbType.VarChar, 30),
                new SqlParameter("@p_PrefixTime", SqlDbType.VarChar, 8),
                new SqlParameter("@p_RSerialNo", SqlDbType.VarChar, 30)};

            parms[0].Value = "SerialNo";
            parms[1].Value = "BillNo";
            parms[2].Value = "AccountsPayable";
            parms[3].Value = curDate.Trim();
            parms[4].Value = billNo;
            parms[4].Direction = ParameterDirection.InputOutput;

            SQLHelper.ExecuteNonQuery(SQLHelper.connectionString, CommandType.Text, cmdString, parms);
            billNo = parms[4].Value.ToString();

            return billNo;
        }

        //检测应付应收单据编号的唯一性
        public bool IsUniqueBillNo(string billNo, int recordId)
        {
            string sqlSelect = "Select count(BillNo) from AccountsPayable where ltrim(rtrim(BillNo)) = @BillNo and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@BillNo", SqlDbType.VarChar, 30),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = billNo.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        public void WriteToDatabase(string action, AccountsPayableInfo accountsPayable, out int recordId)
        {
            string sql = "Exec Usp_ModifyAccountsPayable @p_Action, @p_BillNo, @p_Category, @p_PayDate, @p_Employee, @p_ReferenceNo, @p_Amount, @p_Account, @p_EntryDate, @p_Remark, @p_RecordId out, @p_ErrMsg out, @p_RFlag out";

            SqlParameter[] parms = {
                    new SqlParameter("@p_Action", SqlDbType.Char, 1),
					new SqlParameter("@p_BillNo", SqlDbType.VarChar, 30),
					new SqlParameter("@p_Category", SqlDbType.Int),
                    new SqlParameter("@p_PayDate", SqlDbType.DateTime),
                    new SqlParameter("@p_Employee", SqlDbType.VarChar, 20),
                    new SqlParameter("@p_ReferenceNo", SqlDbType.VarChar, 30),
                    new SqlParameter("@p_Amount", SqlDbType.Decimal, 15),
                    new SqlParameter("@p_Account", SqlDbType.VarChar, 30),
                    new SqlParameter("@p_EntryDate", SqlDbType.DateTime),
                    new SqlParameter("@p_Remark", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RecordId", SqlDbType.Int),
                    new SqlParameter("@p_ErrMsg", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RFlag", SqlDbType.SmallInt)};

            parms[0].Value = action;
            parms[1].Value = accountsPayable.BillNo.Trim();
            parms[2].Value = accountsPayable.Category;
            parms[3].Value = accountsPayable.PayDate;
            parms[4].Value = accountsPayable.Employee.Trim();
            parms[5].Value = accountsPayable.ReferenceNo.Trim();
            parms[6].Value = accountsPayable.Amount;
            parms[7].Value = accountsPayable.Account.Trim();
            parms[8].Value = accountsPayable.EntryDate;
            parms[9].Value = accountsPayable.Remark.Trim();
            parms[10].Value = accountsPayable.RecordId;
            parms[10].Direction = ParameterDirection.InputOutput;
            parms[11].Value = "";
            parms[11].Direction = ParameterDirection.InputOutput;
            parms[12].Value = 0;
            parms[12].Direction = ParameterDirection.InputOutput;

            SQLHelper.ExecuteNonQuery(SQLHelper.connectionString, CommandType.Text, sql, parms);
            recordId = (int)parms[10].Value;
        }
    }
}
